package Dao;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;

import Util.JdbcUtil;

public class CountToday_In_Dao {
	/**
	 *  查询历史的总计的存入金额
	 * @return
	 */
	public double Sum_Pay_Total() {
		double sum = 0;
		try {
			// 建立连接
			Connection conn = JdbcUtil.getConnection();
			String sql = "select round(SUM(pay_total),2) from pay where nullify=0;";
			// 创建语句
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				sum = rs.getDouble(1);
			}
			// 执行语句
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		BigDecimal b = new BigDecimal(sum);  
		sum = b.setScale(2,   BigDecimal.ROUND_HALF_UP).doubleValue();  
		return sum;
	}

	/**
	 *  查询历史的总计的支出金额
	 * @return
	 */
	public double Sum_Refund_Total() {
		double sum = 0;
		try {
			// 建立连接
			Connection conn = JdbcUtil.getConnection();
			String sql = "select SUM(refund_total) from refund where nullify=0;";
			// 创建语句
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				sum = rs.getDouble(1);
			}
			// 执行语句
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		BigDecimal b = new BigDecimal(sum);  
		sum = b.setScale(2,   BigDecimal.ROUND_HALF_UP).doubleValue();  
		return sum;
	}

	/**
	 *  查询今日 的 总计 的 存入金额
	 * @return
	 */
	public double Sum_PayTotal_Today() {
		double sum = 0;
		Date d = new Date();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String dateNowStr = sdf.format(d);
		StringBuffer begin_time = new StringBuffer();
		begin_time.append(dateNowStr);
		begin_time.append(" 00:00:00");
		StringBuffer end_time = new StringBuffer();
		end_time.append(dateNowStr);
		end_time.append(" 23:59:59");
		// 今天的起始时间
		// 今天的结束时间
		try {
			// 建立连接
			Connection conn = JdbcUtil.getConnection();
			String sql = "select SUM(pay_total) from pay where time>='"
					+ begin_time + "' and time<='" + end_time + "' and nullify=0;";
			// 创建语句
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				sum = rs.getDouble(1);
			}
			// 执行语句
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		BigDecimal b = new BigDecimal(sum);  
		sum = b.setScale(2,   BigDecimal.ROUND_HALF_UP).doubleValue(); 
		return sum;
	}

	/**
	 *  查询今日 的 总计 的 存入金额
	 * @return
	 */
	public double Sum_RefundTotal_Today() {
		double sum = 0;
		Date d = new Date();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String dateNowStr = sdf.format(d);
		StringBuffer begin_time = new StringBuffer();
		begin_time.append(dateNowStr);
		begin_time.append(" 00:00:00");
		StringBuffer end_time = new StringBuffer();
		end_time.append(dateNowStr);
		end_time.append(" 23:59:59");
		// 今天的起始时间
		// 今天的结束时间
		try {
			// 建立连接
			Connection conn = JdbcUtil.getConnection();
			String sql = "select SUM(refund_total) from refund where time>='"
					+ begin_time + "' and time<='" + end_time + "' and nullify=0;";
			// 创建语句
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				sum = rs.getDouble(1);
			}
			// 执行语句
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		BigDecimal b = new BigDecimal(sum);  
		sum = b.setScale(2,   BigDecimal.ROUND_HALF_UP).doubleValue();  
		return sum;
	}
	
	/**
	 * 查询今日 存入总量
	 */
	public int Count_PayTotal_Today(){
		int count = 0;
		Date d = new Date();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String dateNowStr = sdf.format(d);
		StringBuffer begin_time = new StringBuffer();
		begin_time.append(dateNowStr);
		begin_time.append(" 00:00:00");
		StringBuffer end_time = new StringBuffer();
		end_time.append(dateNowStr);
		end_time.append(" 23:59:59");
		// 今天的起始时间
		// 今天的结束时间
		String sql = "select COUNT(id) from pay where time>='"+begin_time+"' and time<='"+end_time+"' and nullify=0;";
		
		try {
			// 建立连接
			Connection conn = JdbcUtil.getConnection();
			// 创建语句
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				count = rs.getInt(1);
			}
			// 执行语句
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}
	/**
	 * 查询历史 存入总量
	 */
	public int Count_PayTotal_History(){
		int count = 0;
		String sql = "select COUNT(id) from pay where nullify=0;";
		
		try {
			// 建立连接
			Connection conn = JdbcUtil.getConnection();
			// 创建语句
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				count = rs.getInt(1);
			}
			// 执行语句
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}
	/**
	 * 查询今日 抵用总量
	 */
	public int Count_RefundTotal_Today(){
		int count = 0;
		Date d = new Date();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String dateNowStr = sdf.format(d);
		StringBuffer begin_time = new StringBuffer();
		begin_time.append(dateNowStr);
		begin_time.append(" 00:00:00");
		StringBuffer end_time = new StringBuffer();
		end_time.append(dateNowStr);
		end_time.append(" 23:59:59");
		// 今天的起始时间
		// 今天的结束时间
		String sql = "select COUNT(id) from refund where time>='"+begin_time+"' and time<='"+end_time+"' and nullify=0;";
		
		try {
			// 建立连接
			Connection conn = JdbcUtil.getConnection();
			// 创建语句
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				count = rs.getInt(1);
			}
			// 执行语句
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}
	/**
	 * 查询历史 抵用总量 
	 */
	public int Countr_RefundTotal_History(){
		int count = 0;
		String sql = "select COUNT(id) from refund where nullify=0";
		
		try {
			// 建立连接
			Connection conn = JdbcUtil.getConnection();
			// 创建语句
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				count = rs.getInt(1);
			}
			// 执行语句
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}
	
	/**
	 * 查询 指定时间段内的 抵用 总额
	 */
	public double Sum_RefundTotal_Time(String begin_time,String end_time){
		double sum = 0;
		String sql = "select sum(refund_total) from refund where time>='"+begin_time+"' and time<='"+end_time+"' and nullify=0;";
		try {
			// 建立连接
			Connection conn = JdbcUtil.getConnection();
			// 创建语句
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				sum = rs.getDouble(1);
			}
			// 执行语句
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		BigDecimal b = new BigDecimal(sum);  
		sum = b.setScale(2,BigDecimal.ROUND_HALF_UP).doubleValue();
		return sum;
	}
	/**
	 * 查询 指定时间段内的 存入 总额
	 */
	public double Sum_PayTotal_Time(String begin_time,String end_time){
		double sum = 0;
		String sql = "select sum(pay_total) from pay where time>='"+begin_time+"' and time<='"+end_time+"' and nullify=0;";
		try {
			// 建立连接
			Connection conn = JdbcUtil.getConnection();
			// 创建语句
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				sum = rs.getDouble(1);
			}
			// 执行语句
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		BigDecimal b = new BigDecimal(sum);  
		sum = b.setScale(2,   BigDecimal.ROUND_HALF_UP).doubleValue();
		return sum;
	}
	/**
	 * 查询 指定时间段内的 抵用 总数
	 */
	public int Count_RefundTotal_Time(String begin_time,String end_time){
		int count = 0;
		String sql = "select count(id) from refund where time>='"+begin_time+"' and time<='"+end_time+"' and nullify=0;";
		try {
			// 建立连接
			Connection conn = JdbcUtil.getConnection();
			// 创建语句
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				count = rs.getInt(1);
			}
			// 执行语句
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}
	/**
	 * 查询 指定时间段内的 存入 总数
	 */
	public int Count_PayTotal_Time(String begin_time,String end_time){
		int count = 0;
		String sql = "select count(id) from pay where time>='"+begin_time+"' and time<='"+end_time+"' and nullify=0;";
		try {
			// 建立连接
			Connection conn = JdbcUtil.getConnection();
			// 创建语句
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				count = rs.getInt(1);
			}
			// 执行语句
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}
//	select num from refund where time>='2016-01-13 00:00:00' and time<='2016-01-13 23:59:59' order by num asc limit 1;
	/**
	 * 今天 存入 的第一单的单号
	 * @return
	 */
	public int Sel_Pay_Today_Fist_Num(){
		int num = 0;
		Date d = new Date();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String dateNowStr = sdf.format(d);
		StringBuffer begin_time = new StringBuffer();
		begin_time.append(dateNowStr);
		begin_time.append(" 00:00:00");
		StringBuffer end_time = new StringBuffer();
		end_time.append(dateNowStr);
		end_time.append(" 23:59:59");
		String sql = "select num from pay where time>='"+begin_time+"' and time<='"+end_time+"' order by num asc limit 1;";
		try {
			// 建立连接
			Connection conn = JdbcUtil.getConnection();
			// 创建语句
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				num = rs.getInt(1);
			}
			// 执行语句
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return num;
	}
	
	/**
	 * 今天 存入 的最后一单的单号
	 * @return
	 */
	public int Sel_Pay_Today_End_Num(){
		int num = 0;
		Date d = new Date();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String dateNowStr = sdf.format(d);
		StringBuffer begin_time = new StringBuffer();
		begin_time.append(dateNowStr);
		begin_time.append(" 00:00:00");
		StringBuffer end_time = new StringBuffer();
		end_time.append(dateNowStr);
		end_time.append(" 23:59:59");
		String sql = "select num from pay where time>='"+begin_time+"' and time<='"+end_time+"' order by num desc limit 1;";
		try {
			// 建立连接
			Connection conn = JdbcUtil.getConnection();
			// 创建语句
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				num = rs.getInt(1);
			}
			// 执行语句
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return num;
	}
	
	/**
	 * 今天 抵用 的最后一单的单号
	 */
	public int Sel_Refund_Today_End_Num(){
		int num = 0;
		Date d = new Date();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String dateNowStr = sdf.format(d);
		StringBuffer begin_time = new StringBuffer();
		begin_time.append(dateNowStr);
		begin_time.append(" 00:00:00");
		StringBuffer end_time = new StringBuffer();
		end_time.append(dateNowStr);
		end_time.append(" 23:59:59");
		String sql = "select num from refund where time>='"+begin_time+"' and time<='"+end_time+"' order by num desc limit 1;";
		try {
			// 建立连接
			Connection conn = JdbcUtil.getConnection();
			// 创建语句
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				num = rs.getInt(1);
			}
			// 执行语句
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return num;
	}
	
	/**
	 * 今天 抵用 的最后一单的单号
	 */
	public int Sel_Refund_Today_First_Num(){
		int num = 0;
		Date d = new Date();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String dateNowStr = sdf.format(d);
		StringBuffer begin_time = new StringBuffer();
		begin_time.append(dateNowStr);
		begin_time.append(" 00:00:00");
		StringBuffer end_time = new StringBuffer();
		end_time.append(dateNowStr);
		end_time.append(" 23:59:59");
		String sql = "select num from refund where time>='"+begin_time+"' and time<='"+end_time+"' order by num asc limit 1;";
		try {
			// 建立连接
			Connection conn = JdbcUtil.getConnection();
			// 创建语句
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				num = rs.getInt(1);
			}
			// 执行语句
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return num;
	}
	
	/**
	 * 某天 存入 的第一单的单号
	 */
	public int Sel_Pay_OneDay_First_Num(String begintime,String endtime){
		int num = 0;
		String sql = "select num from pay where time>='"+begintime+"' and time<='"+endtime+"' order by num asc limit 1;";
		try {
			// 建立连接
			Connection conn = JdbcUtil.getConnection();
			// 创建语句
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				num = rs.getInt(1);
			}
			// 执行语句
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return num;
	}
	
	/**
	 * 某天 存入 的最后一单的单号
	 */
	public int Sel_Pay_OneDay_End_Num(String begintime,String endtime){
		int num = 0;
		String sql = "select num from pay where time>='"+begintime+"' and time<='"+endtime+"' order by num desc limit 1;";
		try {
			// 建立连接
			Connection conn = JdbcUtil.getConnection();
			// 创建语句
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				num = rs.getInt(1);
			}
			// 执行语句
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return num;
	}
	
	/**
	 * 某天 抵用 的第一单的单号
	 */
	public int Sel_Refund_OneDay_First_Num(String begintime,String endtime){
		int num = 0;
		String sql = "select num from refund where time>='"+begintime+"' and time<='"+endtime+"' order by num asc limit 1;";
		try {
			// 建立连接
			Connection conn = JdbcUtil.getConnection();
			// 创建语句
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				num = rs.getInt(1);
			}
			// 执行语句
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return num;
	}
	
	/**
	 * 某天 抵用 的最后一单的单号
	 */
	public int Sel_Refund_OneDay_End_Num(String begintime,String endtime){
		int num = 0;
		String sql = "select num from refund where time>='"+begintime+"' and time<='"+endtime+"' order by num desc limit 1;";
		try {
			// 建立连接
			Connection conn = JdbcUtil.getConnection();
			// 创建语句
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				num = rs.getInt(1);
			}
			// 执行语句
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return num;
	}
}